﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ExcelCOM = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
namespace Quanlytruongtruongtieuhoc
{
    class clsXuatfile
    {
        public static void inDanhsach()
        {
            try
            {
                SqlDataAdapter ap = new SqlDataAdapter("select a.ma_gv,ten_cv,ten_td,ten_lv,ho,ltrim(ten) as ten,gioi_tinh,convert(char(10),nam_sinh,103) as nam_sinh,scmnd,dia_chi_ht from giao_vien a,linhvuc_giang_day b,trinh_do c,chuc_vu d,dam_nhiem_chuc_vu e where a.ma_td=c.ma_td and a.ma_lv=b.ma_lv and a.ma_gv=e.ma_gv and e.ma_cv=d.ma_cv and xoa='False' order by a.ma_gv", clsKetnoi.con);
                DataTable test = new DataTable();
                ap.Fill(test);
                ExcelCOM.Application exApp = new ExcelCOM.Application();
                ExcelCOM.Workbook exBook = exApp.Workbooks.Add(ExcelCOM.XlWBATemplate.xlWBATWorksheet);
                ExcelCOM.Worksheet exSheet = (ExcelCOM.Worksheet)exBook.Worksheets[1];

                exSheet.Name = "Danhsach";
                ExcelCOM.Range r0 = (ExcelCOM.Range)exSheet.Cells[1, 1];
                r0.Value2 = "STT";
                r0.Font.Bold = true;
                r0.Font.Italic = true;
                ExcelCOM.Range r1 = (ExcelCOM.Range)exSheet.Cells[1, 2];
                r1.Value2 = "Mã giáo viên";
                r1.Font.Bold = true;
                r1.Font.Italic = true;
                ExcelCOM.Range r2 = (ExcelCOM.Range)exSheet.Cells[1, 3];
                r2.Value2 = "Chức vụ";
                r2.Font.Bold = true;
                r2.Font.Italic = true;
                ExcelCOM.Range r3 = (ExcelCOM.Range)exSheet.Cells[1, 4];
                r3.Value2 = "Trình độ";
                r3.Font.Bold = true;
                r3.Font.Italic = true;
                ExcelCOM.Range r4 = (ExcelCOM.Range)exSheet.Cells[1, 5];
                r4.Value2 = "Lĩnh vực";
                r4.Font.Bold = true;
                r4.Font.Italic = true;
                ExcelCOM.Range r5 = (ExcelCOM.Range)exSheet.Cells[1, 6];
                r5.Value2 = "Họ";
                r5.Font.Bold = true;
                r5.Font.Italic = true;
                ExcelCOM.Range r6 = (ExcelCOM.Range)exSheet.Cells[1, 7];
                r6.Value2 = "Tên";
                r6.Font.Bold = true;
                r6.Font.Italic = true;
                ExcelCOM.Range r7 = (ExcelCOM.Range)exSheet.Cells[1, 8];
                r7.Value2 = "Giới tính";
                r7.Font.Bold = true;
                r7.Font.Italic = true;
                ExcelCOM.Range r8 = (ExcelCOM.Range)exSheet.Cells[1, 9];
                r8.Value2 = "Năm sinh";
                r8.Font.Bold = true;
                r8.Font.Italic = true;
                ExcelCOM.Range r9 = (ExcelCOM.Range)exSheet.Cells[1, 10];
                r9.Value2 = "CMND";
                r9.Font.Bold = true;
                r9.Font.Italic = true;
                ExcelCOM.Range r10 = (ExcelCOM.Range)exSheet.Cells[1, 11];
                r10.Value2 = "Địa chỉ hiện tại";
                r10.Font.Bold = true;
                r10.Font.Italic = true;
                for (int i = 2; i <= test.Rows.Count + 1; i++)
                {
                    for (int j = 2; j <= test.Columns.Count + 1; j++)
                    {
                        DataRow row = test.Rows[i - 2];
                        ExcelCOM.Range r = (ExcelCOM.Range)exSheet.Cells[i, j];
                        r.Value2 = row[j - 2].ToString();
                    }
                    ExcelCOM.Range rs = (ExcelCOM.Range)exSheet.Cells[i, 1];
                    rs.Value2 = (i - 1).ToString();
                }
                string path = "C:\\Users\\Huy\\Desktop\\Danhsach.xls";

                exApp.Visible = false;
                exBook.SaveAs(path, ExcelCOM.XlFileFormat.xlWorkbookNormal,
                    null, null, false, false,
                    ExcelCOM.XlSaveAsAccessMode.xlExclusive,
                    false, false, false, false, false);

                exBook.Close(false, false, false);
                exApp.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);

                MessageBox.Show("Đã export ra file " + path);
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
        }
    }
}
